Advanced usage
https://gyazo.com/981329077192712f87d86f7c77d89eb0
Retrieving assigned auto-generated values
Orm#insertAndGet could be get auto-generated values.
code:java
// TABLE_SQL ="CREATE TABLE IF NOT EXISTS guest (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR, address VARCHAR)";
sorm.acceptHandler(conn -> {
InsertResult<Guest> customer = conn.insertAndGet(Guest.ALICE);
System.out.println(customer.getObject().getId()); //=>1
customer = conn.insertAndGet(Guest.ALICE);
System.out.println(customer.getObject().getId()); //=>2
});
/yuunkjm/--.icon
Handling multiple statement
org.nkjmlab.sorm4j.Sorm handling a OrmConnection with a handler. Sorm applies the handler via accept or applymethod. java.sql.Connection will be closed after executing accept or apply. These methods are suitable for executing multiple SQL statements at getting Connection at once.
OrmConnection wraps an active database javax.sql.Connection. The connection will be closed after the process of handler. If you want to use with JDBC connection.
If your operation will return some result, use Sorm#applyHandler():
code:java
Customer c1 = sorm.applyHandler(conn -> {
conn.insert(new Customer(0));
conn.readFirst(Customer.class, "select * from customer");
});
If your operation does not need to return a result, use Sorm#acceptHandler():
code:java
sorm.acceptHandler(conn -> {
conn.exeuteUpdate(CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR, address VARCHAR));
conn.insert(new Customer(0));
conn.insert(new Customer(1));
});
/yuunkjm/--.icon
Transactions
Lambda expression
Sorm#acceptHandler(int, FunctionHandler) and Sorm#acceptHandler(int, FunctionHandler) methods could handle transactions. The transaction will be rollback after the process of the handler. When the transaction throws an exception, the transaction will be also rollback.
code:java
// Transaction with lambda expression
// The inserted row will be not persisted.
sorm.acceptHandler(Connection.TRANSACTION_READ_COMMITTED, trans -> trans.insert(Customer.ALICE));
// The inserted row will be persisted.
sorm.acceptHandler(Connection.TRANSACTION_READ_COMMITTED, trans -> {
trans.insert(Customer.ALICE);
trans.commit();
});
Try-with-resources
Orm#open method open a transaction. The transaction is automatically rollback when closing the OrmTransaction.
code:java
// Transaction with try-with-resources block. In this way transaction is auto rollback.
try (OrmTransaction trans = sorm.open(Connection.TRANSACTION_READ_COMMITTED)) {
trans.readOne(Integer.class, "select count(*) from customer");
// If the transaction is not committed, it will be automatically rollback when closing the OrmTransaction
trans.insert(Customer.CAROL);
}
/yuunkjm/--.icon
Join
Sorm4j (inner or left) joins two (or three) tables with the given on condition.
code:java
List<Tuple2<Customer, Address>> result = sorm.joinOn(Customer.class, Address.class, "customer.address=address.name");
List<Tuple2<Customer, Address>> result = sorm.leftJoinOn(Customer.class, Address.class, "customer.address=address.name");
Join SQL Builder
code:java
String sql = JoinSql.builder(sorm.getTableMetaData(Customer.class))
.joinOn(sorm.getTableMetaData(Address.class),"customer.address=address.name")
.where("customer.id=?").limit(1);
List<Tuple2<Customer,Address>> result = sorm.join(Customer.class, Address.class, sql, 2);
/yuunkjm/--.icon
Read as tuple list
Sorm4j considers that simple class name + "dot" is the prefix fo column alias. For example, If you use public class Guest{ public int id; public String name; public String address; } as SQL result container class, the id filed is bound to not only the value of column id but also guestdotid. You can also asign column alias prefix explicitly by @OrmColumnAliasPrefix annotation. code:java
List<Tuple2<Guest, Player>> result = sorm.readTupleList(Guest.class, Player.class,
"select g.id as guestdotid, g.name as guestdotname, g.address as guestdotaddress, " +
"p.id as playerdotid, p.name as playerdotname, p.address as playerdotaddress" +
"from guests g join players p on g.id=p.id");
public class Guest{ public int id; public String name; public String address; }
code:java
List<Tuple2<Guest, Player>> result = sorm.readTupleList(Guest.class, Player.class,
"select g.id as gid, g.name as gname, g.address as gaddress, " +
"p.id as pid, p.name as pname, p.address as paddress" +
"from guests g join players p on g.id=p.id");
@OrmColumnAliasPrefix("g") public class Guest {}
@OrmColumnAliasPrefix("p") public class Player {}
/yuunkjm/--.icon
RowMapper
Sorm4j could automatically map a row to an object, but you can also assign your mapping function via RowMapper.
code:exaplme.java
sorm.executeQuery(ParameterizedSql.of("select * from players"),
(ResultSet rs, int index) ->
new Player(rs.getInt("id"), rs.getString("name"), rs.getString("address")));
Sorm4j also could automatically map a ResultSet to an object, but you can also assign your mapping function via ResultSetTraverser.
/yuunkjm/--.icon
Select SQL builder
Sorm4j provides simple select string builder.
code:java
String sql = SelectSql.builder().where("name=? and address=?").addParameter("Alice", "Kyoto").build();
select: sets column names. The default value is *.
from: sets the table name. The value automatically sets based on the given class.
where: sets the where clause.
addParameter: sets ordered parameters.
/yuunkjm/--.icon
Logging
Sorm4j uses logging facade Log4j2 API or SLF4J API. If you want to use logging functions, you install a logger implementation. code:pom.xml
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-core</artifactId><version>2.17.1</version></dependency>
or
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-slf4j-impl</artifactId><version>2.17.1</version></dependency>
Logging could be controlled by org.nkjmlab.sorm4j.Sorm.Builder. If you want to log about executeQuery, call on method:
code:Example.java
Sorm.Builder#setLoggerOn(Category.EXECUTE_QUERY);
Log category
MAPPING: Logs object-table mapping and object-columns mapping.
EXECUTE_QUERY: Logs executeQuery (e.g. read, readList, selectAll and so on.)
MULTI_ROW: Logs multi-row insert/merge SQL
EXECUTE_UPDATE: Logs executeUpdate excepts multirow insert/merge SQL (e.g. insert, update, delete and so on.)
/yuunkjm/--.icon
Annotations
There are a few annotations that control the mapping behavior from classes to database tables.
@OrmTable can be associated with a class and specify the name of the table that the class is mapped to.
@OrmConstructor can be associated with constructor parameters and specify the names of the column.
@OrmColumn can be associated with a field and specify the name of the column.
@OrmGetter can be associated with a field and specify the getter of the column.
@OrmSetter can be associated with a field and specify the setter of the column.
@OrmIgnore can be associated with a setter/getter/field specify it should not be mapped to a column in the database (by default, Sorm4j attempts to map all fields of a given class to columns in the table associated with their class).
/yuunkjm/--.icon
Working with other JDBC connection
Sorm4j uses an object it simply wraps a java.sql.Connection object for object-relation mapping.
code:java
String jdbcUrl = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;"; String user = "user"; String password = "pwd";
try (Connection jdbcConn = DriverManager.getConnection(jdbcUrl, user, password);
OrmConnection conn = OrmConnection.of(jdbcConn)) {
List<Customer> allCustomers = conn.selectAll(Customer.class);
}
/yuunkjm/--.icon
Customized implmentation
DefaultColumnValueToJavaObjectConverters could be register Map<Class<?>, ColumnValueToJavaObjectConverter>. The instance of ColumnValueToJavaObjectConverter depending on the parameter class.
DefaultSqlParametersSetter could be register Map<Class<?>, SqlParameterSetter> . The instance of SqlParameterSetter depending on the parameter class.